package DBI::Simply;
require v5.8.6;
our $VERSION = 0.1;
require DBI;
use Iterator;
use strict;

sub new {
  my( $class, $dbh_producer ) = @_;
  bless [ undef, $dbh_producer ], $class;
}

sub __SQL {
  my( $class, $name, $param_types, $statement ) = @_;
  my $qname = $class . '::' . $name;
  { no strict;
    *$qname = sub{
      use strict;
      my( $this ) = @_;
      my $sth = $this->dbh->prepare( $statement )
	|| die qq{FAILED preparing $qname: $DBI::errstr};
      sub {
	my @params = @_;
	for( my $i=0; $i<@params; $i++) {
	  $sth->bind_param( $i+1, $params[$i], $param_types->[$i] )
	    || die qq{FAILED binding $qname: $DBI::errstr};
	}
	$sth->execute || die qq{FAILED executing $qname: $DBI::errstr};
        if( $sth->{NUM_OF_FIELDS} ) {
	  !defined(wantarray) ? $sth->finish :
	    Iterator->new
	      ( sub{
		  my @r = @{ $sth->fetchrow_arrayref || Iterator::is_done };
		    \@r;
		}
	      );
	} else {
	  undef;
	}
      }
    }
  }
}

sub dbh {
  my( $this ) = @_;
  my( $dbh, $producer ) = @$this;
  $this->[0] = $producer->() unless $dbh
                                and 'DBI::db' eq ref $dbh
                                and $dbh->ping;
  $this->[0];
}

1;
__END__

=head1 NAME

DBI::Simply - Fasade to DBI statements with resultset iterators.

=head1 SYNOPSIS

  package MyModel;
  use base 'DBI::Simply';

  __PACKAGE__->__SQL(
  insert_book => [ SQL_INTEGER, SQL_CHAR, SQL_CHAR ] => <<'SQL' );
  insert into books (id, name, author)
             values ( ?,    ?,      ?)
  SQL

  __PACKAGE__->__SQL(
  all_books => [] => <<'SQL' );
  select name, author
    from books
  SQL

  __PACKAGE__->__SQL(
  author_books => [ SQL_CHAR ] => <<'SQL' );
  select name, author
    from books
   where author like ?
  SQL


  package main;
  use DBD::DB2;
  use MyModel;
  use Other;   # some module that knows how to handle iterators

  my $model = MyModel->new( sub{ DBI->connect( ... ) } );

  # simply execute
  $model->insert_books->( 1, 'War and Peace', 'Leo Tolstoy' );

  my $inserter = $model->insert_books; # prepare for many inserts
  $inserter->(@$_) for (
    [ 2,        'Anna Karenina',        'Leo Tolstoy' ],
    [ 3, 'Crime and Punishment', 'Fyodor Dostoyevsky' ],
  );

  $model->dbh->commit; # DBI::Simply is transaction ignorant

  my $books = $model->all_books->(); # get resultset iterator
  while( $books->isnt_exhausted ) {
    my $row = $books->value();
    print "$row->[1] wrote $row->[0]\n";
  }

  my $like = $model->author_books;      # prepare for many selects
  Other->method1( $like->('Leo%'   ) ); # pass resultset iterators around
  Other->method2( $like->('Fyodor%') );

=head1 MOTIVATION

ORMs have learning curves, they impose artificial limits inherited
from whatever OO they are trying to adapt to, and throw away enormous
benefit that relational model gives.  And what about that time you
spent to learn SQL?  True, SQL is not the top model it has been
dressed like, but it can get a lot closer to a pure relational model
than any ORM will. L<DBI::Simply> is L<DBI> with makeup on.  No drama,
just closures.

Your DB allows you to use XQuery? (or any other super-duper
non-standard bells-and-whistles) No problem, just use it.

All your SQL is neatly tucked into it's own package(s) and you use it
with another package.

=head1 new

The constructor of your model.  Pass DB handle producer to it in the
like manner:

  my $model = DBI::Simply->new( sub{ DBI->connect( ... ) } );

=head1 __SQL

always call it from the C<__PACKAGE__> context in your model module
that inherits from L<DBI::Simply>.

First argument is a subroutine name that will be generated in the
C<__PACKAGE__>.  That subroutine returns a subref with prepared statement which in turn takes the statement arguments, binds them to the statement, executes it, and returns an L<Iterator> to the resultset if needed.

Second argument is a list reference to parameter datatypes that will
be bound for each execution of the SQL statement.  These are listed in
the sourcecode of DBI package.  Most likely the only needed
distinction is between numeric and string types.

Third argument is a string representing the SQL statement.  I like to
use heredocs for that.  Remember, default heredocs do interpolate.
Single quoted heredocs are probably the best pick.  Avoid SQL
injections, let DBI handle escapes.

=head1 BUGS

Some databases cannot prepare and hold more than one statement at a
time.  Others cannot interlace fetching two or more resultsets.  Know
your database.

Handle transactions yourself with the DB handle.  Maximum control,
know your database.

=head1 AUTHOR

Eugene Grigoriev,

  let a b c d e f g  = concat [e,f,d,g,c,f,b] in
      a "com" "gmail" "grigoriev" "eugene" "." "@"

=head1 LICENSE

BSD

=cut
